package com.gemcoder.system.service;
import static com.gemcoder.system.datasource.JdbcTemplateUtil.executeSQL;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;
import org.springframework.stereotype.Service;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserName;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserId;
import com.gemcoder.system.bo.Result;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllPermissions;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllMenus;
import java.sql.SQLException;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.core.date.DateUtil;
import cn.hutool.db.Db;
import cn.hutool.db.handler.EntityListHandler;
import cn.hutool.db.sql.SqlUtil;
import cn.hutool.db.handler.NumberHandler;

@Service
public class ProjecteconomicstatementService {

    public Result getProjectEconomicData(Map<String,Object> paramMap){
    // 获取参数
    String projectName = paramMap.get("ProjectNamevalue1") != null ? String.valueOf(paramMap.get("ProjectNamevalue1")) : null;
    String projectNumber = paramMap.get("ProjectNumbervalue1") != null ? String.valueOf(paramMap.get("ProjectNumbervalue1")) : null;
    String monthly = paramMap.get("Monthlyvalue1") != null ? String.valueOf(paramMap.get("Monthlyvalue1")) : null;
    Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
    Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;

    // 构建查询SQL
    StringBuilder sql = new StringBuilder("SELECT per.id, p.project_name, p.project_code, per.generated_date, per.operation FROM project_economic_report per JOIN project p ON per.project_id = p.id WHERE 1=1");
    List<Object> params = new ArrayList<>();

    if (projectName != null && !projectName.isEmpty()) {
        sql.append(" AND p.project_name LIKE ?");
        params.add("%" + projectName + "%");
    }
    if (projectNumber != null && !projectNumber.isEmpty()) {
        sql.append(" AND p.project_code LIKE ?");
        params.add("%" + projectNumber + "%");
    }
    if (monthly != null && !monthly.isEmpty()) {
        sql.append(" AND DATE_FORMAT(per.generated_date, '%Y-%m') = ?");
        params.add(monthly);
    }

    // 分页查询
    int offset = (current - 1) * pageSize;
    sql.append(" LIMIT ?, ?");
    params.add(offset);
    params.add(pageSize);

    // 执行查询
    List<Map<String, Object>> data = executeSQL(sql.toString(), params.toArray());

    // 查询总条数
    StringBuilder countSql = new StringBuilder("SELECT COUNT(*) as total FROM project_economic_report per JOIN project p ON per.project_id = p.id WHERE 1=1");
    List<Object> countParams = new ArrayList<>();
    if (projectName != null && !projectName.isEmpty()) {
        countSql.append(" AND p.project_name LIKE ?");
        countParams.add("%" + projectName + "%");
    }
    if (projectNumber != null && !projectNumber.isEmpty()) {
        countSql.append(" AND p.project_code LIKE ?");
        countParams.add("%" + projectNumber + "%");
    }
    if (monthly != null && !monthly.isEmpty()) {
        countSql.append(" AND DATE_FORMAT(per.generated_date, '%Y-%m') = ?");
        countParams.add(monthly);
    }
    List<Map<String, Object>> countResult = executeSQL(countSql.toString(), countParams.toArray());
    int total = countResult.get(0).get("total") != null ? Integer.valueOf(String.valueOf(countResult.get(0).get("total"))) : 0;

    // 返回结果
    Map<String, Object> result = new HashMap<>();
    result.put("total", total);
    result.put("data", data);
    return new Result(true, "查询成功", result);
}

public Result exportProjectEconomicData(Map<String,Object> paramMap){
    // 获取参数
    Integer id = paramMap.get("id") != null ? Integer.valueOf(String.valueOf(paramMap.get("id"))) : null;

    // 构建查询SQL
    StringBuilder sql = new StringBuilder("SELECT per.id, p.project_name, p.project_code, per.generated_date, per.operation FROM project_economic_report per JOIN project p ON per.project_id = p.id WHERE per.id = ?");
    List<Object> params = new ArrayList<>();
    params.add(id);

    // 执行查询
    List<Map<String, Object>> data = executeSQL(sql.toString(), params.toArray());

    // 导出逻辑（假设导出为CSV文件）
    // 这里可以使用hutool的CsvUtil进行导出
    // CsvUtil.getWriter().write(data);

    // 返回结果
    return new Result(true, "导出成功", data);
}

public Result exportCurrentMonthProjectEconomicData(Map<String,Object> paramMap){
    // 获取参数
    String projectName = paramMap.get("projectName") != null ? String.valueOf(paramMap.get("projectName")) : null;
    String monthly = paramMap.get("Monthlyvalue1") != null ? String.valueOf(paramMap.get("Monthlyvalue1")) : null;

    // 构建查询SQL
    StringBuilder sql = new StringBuilder("SELECT por.id, p.project_name, p.project_code, por.generated_date, por.operation FROM project_economic_report por JOIN project p ON por.project_id = p.id WHERE p.project_name = ? AND DATE_FORMAT(por.generated_date, '%Y-%m') = ?");
    List<Object> params = new ArrayList<>();
    params.add(projectName);
    params.add(monthly);

    // 执行查询
    List<Map<String, Object>> data = executeSQL(sql.toString(), params.toArray());

    // 导出逻辑（假设导出为CSV文件）
    // 这里可以使用hutool的CsvUtil进行导出
    // CsvUtil.getWriter().write(data);

    // 返回结果
    return new Result(true, "导出成功", data);
}

}